<?php
defined('BASEPATH') OR exit('No direct script access allowed');

class data_service{
	private $CI;
	function __construct() { 
		$this->CI = &get_instance();
		$this->CI->load->model("Academia_job_model");
		$this->CI->load->model("Academic_report_model");
		$this->CI->load->model("Audit_model");
		$this->CI->load->model("Awards_model");
		$this->CI->load->model("Communication_model");
		$this->CI->load->model("Horizontal_project_model");
		$this->CI->load->model("Monograph_model");
		$this->CI->load->model("Paper_model");
		$this->CI->load->model("Patent_data_model");
		$this->CI->load->model("Patent_model");
		$this->CI->load->model("Research_report_model");
		$this->CI->load->model("Science_conversion_model");
		$this->CI->load->model("Technical_standard_model");
		$this->CI->load->model("Vertical_project_model");
		$this->CI->load->model("Admin_model");
	}

	/**  
	* 获取折线图数据统计
	* @access public 
	* @param  $type  成果类型
	* @param  $data_id  成果id
	*/  
	function getDatas($param=false)
	{	
		$role = explode(',', getSess()['sub_role_id']);
       	 array_push($role, getSess()['role_id']);

		// 部门筛选
		if(isset($param['dept']) && $param['dept']!=-1){
			if (strpos($param['dept'],'分院') !== false) {
				$deptname = strstr($param['dept'], '分院', true);
				$where = " dept_name like '%".$deptname."%'";
			}else if($param['dept'] == '职能管理部'){
				$dept_data = array();
				foreach ((new addDept())->getAll() as $k => $v) {
					if($v[XPHEnum::Alternate] > 100 && $v[XPHEnum::Alternate] < 200){
						$dept_data[$k]= $v[XPHEnum::Caption];
					}
				}
				$dept_data[count($dept_data)] = '职能管理部';
			   $deptnames = implode("','",$dept_data);
			   $where  = " dept_name in ('".$deptnames."')";

			}else{
				$where  = $this->CI->sqlEscape(' dept_name=?? ', array($param['dept'])); 
			}
			// $where=$this->CI->sqlEscape('dept_name=??',array($param['dept']));
		}else{			
			if(in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)||in_array(WebRole::Sscientifictaff[XPHEnum::Value], $role)){
				$dept_data = array();
				foreach ((new Dept())->getAll() as $k => $v) {
					$dept_data[$k]['dept_name'] = $v[XPHEnum::Caption];
				}
			}else{      	
				$connar['items']="dept_name";
				$connar['groupby']="dept_name";
				$connar['where']=$this->CI->sqlEscape("status=??",array(CommonStatus::UnDeleted[XPHEnum::Value]));
				$dept_data=$this->CI->Admin_model->get_list_full($connar);
			}
	    	$dept_data= array_values(array_filter(array_column($dept_data, 'dept_name')));

			$dept_data1 = array(); $case = "when dept_name = '公共基础教学部（体育部）、马克思主义学院' then '公共基础教学部'";
			foreach ((new addDept())->getAll() as $k => $v) {
				if($v[XPHEnum::Alternate] > 100 && $v[XPHEnum::Alternate] < 200){
					$dept_data1[$k]= $v[XPHEnum::Caption];
					$case .= "when dept_name = '".$v[XPHEnum::Caption]."' then '职能管理部'";
				}
			}
			$dept_data = array_merge($dept_data, $dept_data1);

			$dept="'".implode("','",$dept_data)."'";
	    	// $dept='';  	
	    	// foreach ($dept_data as $k => $v) {
	    	// 	if($k==0){
	    	// 		$dept="'".$v."'";
	    	// 	}else{
	    	// 		$dept.=",'".$v."'";	    			
	    	// 	}
	    	// };
			$where="dept_name in(".$dept.")";
		}		
		$where.=$this->CI->sqlEscape(' and status=?? and is_submit_cg=?? and is_del=??',array(ResultState::Publiced[XPHEnum::Value],AuditType::First[XPHEnum::Value],CommonWhether::Not[XPHEnum::Value]));
		// 类型筛选
		if(isset($param['type'])&& $param['type']!=-1){
			$type=array($param['type']);
		}else{
			$type=(New ResultsType)->getAllValue();
		}
		/* ------------------------------------论文------------------------------------------------*/
		if(in_array(ResultsType::First[XPHEnum::Value], $type)){
			$data1['items']="if(publish_time,FROM_UNIXTIME(publish_time,'%Y'),'--') years,count(*) count";
			$data1['groupby']="years";
			$data1['orderby']="publish_time asc";
			$data1['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data1['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(publish_time,'%Y')=??",array($param['year']));
			}
			if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data1['where'] .= $this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and author=?? ', array($param['name']));
	  //       }
			$data1=$this->CI->Paper_model->get_list_full($data1);

			$data1_title=array_column($data1, 'years');
			$data1_data=array_column($data1, 'count');
			$data[]=array('table_name'=>reset($data1_title).'-'.end($data1_title).ResultsType::First[XPHEnum::Caption],'data'=>$data1_data,'title'=>$data1_title);
		}
		/* ------------------------------------专著------------------------------------------------*/
		if(in_array(ResultsType::Second[XPHEnum::Value], $type)){
			$data2['items']="FROM_UNIXTIME(publish_time,'%Y') years,count(*) count";
			$data2['groupby']="years";
			$data2['orderby']="publish_time asc";
			$data2['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data2['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(publish_time,'%Y')=??",array($param['year']));
			}
			if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data2['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
	        // if (isset($param['name'])) {
	        //     $connar['where'] .= $this->CI->sqlEscape(' and author=?? ', array($param['name']));
	        // }
			$data2=$this->CI->Monograph_model->get_list_full($data2);
			$data2_title=array_column($data2, 'years');
			$data2_data=array_column($data2, 'count');
			$data[]=array('table_name'=>reset($data2_title).'-'.end($data2_title).ResultsType::Second[XPHEnum::Caption],'data'=>$data2_data,'title'=>$data2_title);
		}
		// /* ------------------------------------专利------------------------------------------------*/
		if(in_array(ResultsType::Third[XPHEnum::Value], $type)){
			$data3['items']="FROM_UNIXTIME(announcement_date,'%Y') years,count(*) count";
			$data3['groupby']="years";
			$data3['orderby']="announcement_date asc";
			$data3['where']=$where;
	        // if (isset($param['name'])) {
	        //     $connar['where'] .= $this->CI->sqlEscape(' and author=?? ', array($param['name']));
	        // }
			if(isset($param['year']) && $param['year']!=''){
				$data3['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(announcement_date,'%Y')=??",array($param['year']));
			}
			if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data3['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			$data3=$this->CI->Patent_model->get_list_full($data3);
			$data3_title=array_column($data3, 'years');
			$data3_data=array_column($data3, 'count');
			$data[]=array('table_name'=>reset($data3_title).'-'.end($data3_title).ResultsType::Third[XPHEnum::Caption],'data'=>$data3_data,'title'=>$data3_title);
		}
		
		// /* ------------------------------------获奖------------------------------------------------*/
		if(in_array(ResultsType::Fourth[XPHEnum::Value], $type)){			
			$data4['items']="FROM_UNIXTIME(award_time,'%Y') years,count(*) count";
			$data4['groupby']="years";
			$data4['orderby']="award_time asc";
			$data4['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data4['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(award_time,'%Y')=??",array($param['year']));
			}
			if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data4['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
	        // if (isset($param['name'])) {
	        //     $connar['where'] .= $this->CI->sqlEscape(' and main_complete=?? ', array($param['name']));
	        // }
			$data4=$this->CI->Awards_model->get_list_full($data4);
			$data4_title=array_column($data4, 'years');
			$data4_data=array_column($data4, 'count');
			$data[]=array('table_name'=>reset($data4_title).'-'.end($data4_title).ResultsType::Fourth[XPHEnum::Caption],'data'=>$data4_data,'title'=>$data4_title);
		}
		// /* ------------------------------------研究报告、政策建议------------------------------------------------*/
		if(in_array(ResultsType::Fifth[XPHEnum::Value], $type)){
			$data5['items']="FROM_UNIXTIME(submit_date,'%Y') years,count(*) count";
			$data5['groupby']="years";
			$data5['orderby']="submit_date asc";
			$data5['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data5['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(submit_date,'%Y')=??",array($param['year']));
			}
			if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data5['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and author=?? ', array($param['name']));
	  //       }
			$data5=$this->CI->Research_report_model->get_list_full($data5);
			$data5_title=array_column($data5, 'years');
			$data5_data=array_column($data5, 'count');
			$data[]=array('table_name'=>reset($data5_title).'-'.end($data5_title).ResultsType::Fifth[XPHEnum::Caption],'data'=>$data5_data,'title'=>$data5_title);
		}
		
		// /* ------------------------------------技术标准------------------------------------------------*/
		if(in_array(ResultsType::Sixth[XPHEnum::Value], $type)){
			$data6['items']="FROM_UNIXTIME(publish_time,'%Y') years,count(*) count";
			$data6['groupby']="years";
			$data6['orderby']="publish_time asc";
			$data6['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data6['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(publish_time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data6['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and completeor=?? ', array($param['name']));
	  //       }
			$data6=$this->CI->Technical_standard_model->get_list_full($data6);
			$data6_title=array_column($data6, 'years');
			$data6_data=array_column($data6, 'count');
			$data[]=array('table_name'=>reset($data6_title).'-'.end($data6_title).ResultsType::Sixth[XPHEnum::Caption],'data'=>$data6_data,'title'=>$data6_title);
		}
		
		// /* ------------------------------------纵向课题------------------------------------------------*/
		if(in_array(ResultsType::Seventh[XPHEnum::Value], $type)){
			$data7['items']="FROM_UNIXTIME(project_time,'%Y') years,count(*) count";
			$data7['groupby']="years";
			$data7['orderby']="project_time asc";
			$data7['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data7['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(project_time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data7['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and head_name=?? ', array($param['name']));
	  //       }
			$data7=$this->CI->Vertical_project_model->get_list_full($data7);
			$data7_title=array_column($data7, 'years');
			$data7_data=array_column($data7, 'count');
			$data[]=array('table_name'=>reset($data7_title).'-'.end($data7_title).ResultsType::Seventh[XPHEnum::Caption],'data'=>$data7_data,'title'=>$data7_title);
		}
		
		// /* ------------------------------------横向课题------------------------------------------------*/
		if(in_array(ResultsType::Eighth[XPHEnum::Value], $type)){
			$data8['items']="FROM_UNIXTIME(project_time,'%Y') years,count(*) count";
			$data8['groupby']="years";
			$data8['orderby']="project_time asc";
			$data8['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data8['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(project_time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data8['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and head_name=?? ', array($param['name']));
	  //       }
			$data8=$this->CI->Horizontal_project_model->get_list_full($data8);
			$data8_title=array_column($data8, 'years');
			$data8_data=array_column($data8, 'count');
			$data[]=array('table_name'=>reset($data8_title).'-'.end($data8_title).ResultsType::Eighth[XPHEnum::Caption],'data'=>$data8_data,'title'=>$data8_title);
		}
		
		// /* ------------------------------------科技转化情况------------------------------------------------*/
		if(in_array(ResultsType::Ninth[XPHEnum::Value], $type)){
			$data9['items']="FROM_UNIXTIME(time,'%Y') years,count(*) count";
			$data9['groupby']="years";
			$data9['time']="announcement_date asc";
			$data9['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data9['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data9['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and head_name=?? ', array($param['name']));
	  //       }
			$data9=$this->CI->Science_conversion_model->get_list_full($data9);
			$data9_title=array_column($data9, 'years');
			$data9_data=array_column($data9, 'count');
			$data[]=array('table_name'=>reset($data9_title).'-'.end($data9_title).ResultsType::Ninth[XPHEnum::Caption],'data'=>$data9_data,'title'=>$data9_title);
		}
		
		// /* ------------------------------------举办、参加学术交流会、学会年会------------------------------------------------*/
		if(in_array(ResultsType::Tenth[XPHEnum::Value], $type)){
			$data10['items']="FROM_UNIXTIME(time,'%Y') years,count(*) count";
			$data10['groupby']="years";
			$data10['time']="announcement_date asc";
			$data10['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data10['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data10['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           // 没有找到成果人字段
	  //           $connar['where'] .= $this->CI->sqlEscape(' and 1<>?? ', array(1));
	  //       }
			$data10=$this->CI->Communication_model->get_list_full($data10);
			$data10_title=array_column($data10, 'years');
			$data10_data=array_column($data10, 'count');
			$data[]=array('table_name'=>reset($data10_title).'-'.end($data10_title).ResultsType::Tenth[XPHEnum::Caption],'data'=>$data10_data,'title'=>$data10_title);
		}
		
		// /* ------------------------------------学术报告情况------------------------------------------------*/
		if(in_array(ResultsType::Eleventh[XPHEnum::Value], $type)){
			$data11['items']="FROM_UNIXTIME(time,'%Y') years,count(*) count";
			$data11['groupby']="years";
			$data11['orderby']="time asc";
			$data11['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data11['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(time,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data11['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
			// 	// 没有找到成果人字段
	  //           $connar['where'] .= $this->CI->sqlEscape(' and 1<>?? ', array(1));
	  //       }
			$data11=$this->CI->Academic_report_model->get_list_full($data11);
			$data11_title=array_column($data11, 'years');
			$data11_data=array_column($data11, 'count');
			$data[]=array('table_name'=>reset($data11_title).'-'.end($data11_title).ResultsType::Eleventh[XPHEnum::Caption],'data'=>$data11_data,'title'=>$data11_title);
		}
				
		// /* ------------------------------------学术兼职情况------------------------------------------------*/
		if(in_array(ResultsType::Twelfth[XPHEnum::Value], $type)){
			$data12['items']="FROM_UNIXTIME(created,'%Y') years,count(*) count";
			$data12['groupby']="years";
			$data12['orderby']="created asc";//没有兼职时间，这里暂时使用创建时间
			$data12['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data12['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(created,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data12['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and name=?? ', array($param['name']));
	  //       }
			$data12=$this->CI->Academia_job_model->get_list_full($data12);
			$data12_title=array_column($data12, 'years');
			$data12_data=array_column($data12, 'count');
			$data[]=array('table_name'=>reset($data12_title).'-'.end($data12_title).ResultsType::Twelfth[XPHEnum::Caption],'data'=>$data12_data,'title'=>$data12_title);
		}
		// /* ------------------------------------学科平台-----------------------------------------------*/
		if(in_array(ResultsType::Thirteen[XPHEnum::Value], $type)){
			$data13['items']="FROM_UNIXTIME(created,'%Y') years,count(*) count";
			$data13['groupby']="years";
			$data13['orderby']="created asc";//没有兼职时间，这里暂时使用创建时间
			$data13['where']=$where;
			if(isset($param['year']) && $param['year']!=''){
				$data13['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(created,'%Y')=??",array($param['year']));
			}if(isset($param['submit_year']) && $param['submit_year']!=''){
				$data13['where'].=$this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
			}
			// if (isset($param['name'])) {
	  //           $connar['where'] .= $this->CI->sqlEscape(' and name=?? ', array($param['name']));
	  //       }
			$data13=$this->CI->Subject_platform_model->get_list_full($data13);
			$data13_title=array_column($data13, 'years');
			$data13_data=array_column($data13, 'count');
			$data[]=array('table_name'=>reset($data13_title).'-'.end($data13_title).ResultsType::Thirteen[XPHEnum::Caption],'data'=>$data13_data,'title'=>$data13_title);
		}
		
		
		if(!isset($data)){
			$data=array();
		}

		return retJson('图标数据',true,$data);
	}


	/**  
	* 获取柱状图数据统计
	* @access public 
	* @param  $type  成果类型
	* @param  $data_id  成果id
	*/  
	function getDatas1($param=false)
	{
		  $role = explode(',', getSess()['sub_role_id']);
        array_push($role, getSess()['role_id']);

        if(in_array(WebRole::SchoolLeader[XPHEnum::Value], $role)||in_array(WebRole::Sscientifictaff[XPHEnum::Value], $role)){
            $dept_data = array();
            foreach ((new Dept())->getAll() as $k => $v) {
                $dept_data[$k]['dept_name'] = $v[XPHEnum::Caption];
            }
        }else{      	
			$connar['items']="dept_name";
	    	$connar['groupby']="dept_name";
	    	$connar['where']=$this->CI->sqlEscape("status=??",array(CommonStatus::UnDeleted[XPHEnum::Value]));
	    	$dept_data=$this->CI->Admin_model->get_list_full($connar);
	    }
    	$dept_data=array_column($dept_data, 'dept_name');

		$dept_data1 = array(); $case = "when dept_name = '公共基础教学部（体育部）、马克思主义学院' then '公共基础教学部'";
		foreach ((new addDept())->getAll() as $k => $v) {
			if($v[XPHEnum::Alternate] > 100 && $v[XPHEnum::Alternate] < 200){
				$dept_data1[$k]= $v[XPHEnum::Caption];
				$case .= "when dept_name = '".$v[XPHEnum::Caption]."' then '职能管理部'";
			}
		}
		$dept_data = array_merge($dept_data, $dept_data1);

		$dept="'".implode("','",$dept_data)."'";
    
		$where="dept_name in(".$dept.")";
		$where.=$this->CI->sqlEscape(' and status=?? and is_del=?? and is_submit_cg =?? ',array(ResultState::Publiced[XPHEnum::Value],CommonWhether::Not[XPHEnum::Value],AuditType::First[XPHEnum::Value]));
		
		if(isset($param['dept']) && $param['dept']!='-1'){
			if (strpos($param['dept'],'分院') !== false) {
				$deptname = strstr($param['dept'], '分院', true);
				$where .= " and dept_name like '%".$deptname."%'";
			}else if($param['dept'] == '职能管理部'){
				$dept_data = array();
				foreach ((new addDept())->getAll() as $k => $v) {
					if($v[XPHEnum::Alternate] > 100 && $v[XPHEnum::Alternate] < 200){
						$dept_data[$k]= $v[XPHEnum::Caption];
					}
				}
				$dept_data[count($dept_data)] = '职能管理部';
			   $deptnames = implode("','",$dept_data);
			   $where  .= " and dept_name in ('".$deptnames."')";

			}else{
				$where  .= $this->CI->sqlEscape(' and dept_name=?? ', array($param['dept'])); 
			}
		}


		if(isset($param['type']) && $param['type']!='-1'){
			$type=array($param['type']);
		}else{
			$type=(new ResultsType)->getAllValue();
		}

		
		$caseitems = "(case ".$case." else dept_name end ) as dept_name,";
		$orderby = "FIELD(dept_name,'机电分院','机械工程分院','电气电子工程分院','建筑工程分院','财经分院','商贸分院','交通分院','设计与艺术分院','鉴湖分院','人文社科部','公共基础教学部','职能管理部','外单位','校领导')";

		/* ------------------------------------论文------------------------------------------------*/
		if(in_array(ResultsType::First[XPHEnum::Value], $type)) {
			$data1['items']=$caseitems."IF(db_paper.publish_time='','--',DATE_FORMAT( FROM_UNIXTIME( db_paper.publish_time), '%Y' )) year_date,db_paper.id";
			$data1['where']=$where;
            $data1['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::First[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_paper.id');
            if (isset($param['year']) && $param['year'] != '') {
                $data1['where'] .= $this->CI->sqlEscape(' and DATE_FORMAT(FROM_UNIXTIME(publish_time), "%Y" ) =?? ', array($param['year']));
            }
            if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data1['where'] .= $this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
            }

			if (isset($param['name'])  && $param['name']!=-1 && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data1['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::First[XPHEnum::Value].') author1','author1.data_id=db_paper.id');
                 $data1['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data1=$this->CI->Paper_model->get_list_full($data1);
				//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Paper_model',$orderby,ResultsType::First[XPHEnum::Caption]); //获取返回数据
			
		}
				
		/* ------------------------------------专著------------------------------------------------*/
		if(in_array(ResultsType::Second[XPHEnum::Value], $type)) {
			$data2['items']=$caseitems."if(db_monograph.publish_time='','--',FROM_UNIXTIME(db_monograph.publish_time,'%Y')) year_date";
			$data2['where']=$where;
            $data2['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Second[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_monograph.id');
			if(isset($param['year']) && $param['year']!=''){
				$data2['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_monograph.publish_time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data2['where'] .= $this->CI->sqlEscape(' and db_monograph.award_year=?? ', array($param['submit_year']));
            }
	        if (isset($param['name'])  && $param['name']!=-1) {
	        	$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data2['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Second[XPHEnum::Value].') author1','author1.data_id=db_monograph.id');
                 $data2['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data2=$this->CI->Monograph_model->get_list_full($data2);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Monograph_model',$orderby,ResultsType::Second[XPHEnum::Caption]); //获取返回数据

		}
		/* ------------------------------------专利------------------------------------------------*/
		if(in_array(ResultsType::Third[XPHEnum::Value], $type)) {
			$data3['items']=$caseitems."if(db_patent.announcement_date='','--',FROM_UNIXTIME(db_patent.announcement_date,'%Y'))  year_date";
			$data3['where']=$where;
            $data3['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Third[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_patent.id');
			if(isset($param['year']) && $param['year']!=''){
				$data3['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_patent.announcement_date,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data3['where'] .= $this->CI->sqlEscape(' and db_patent.award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data3['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Third[XPHEnum::Value].') author1','author1.data_id=db_patent.id');
                 $data3['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	          //  $data3['where'] .= $this->CI->sqlEscape('  and find_in_set(??,author.author) and find_in_set(??,author.identity)', array($name, $identity));
	        }
			$data3=$this->CI->Patent_model->get_list_full($data3);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Patent_model',$orderby,ResultsType::Third[XPHEnum::Caption]); //获取返回数据
		}
		
		/* ------------------------------------获奖------------------------------------------------*/
		if(in_array(ResultsType::Fourth[XPHEnum::Value], $type)) {
			$data4['items']=$caseitems."if(db_awards.award_time='','--',FROM_UNIXTIME(db_awards.award_time,'%Y'))  year_date";
			$data4['where']=$where;
            $data4['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Fourth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_awards.id');
			if(isset($param['year']) && $param['year']!=''){
				$data4['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(award_time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data4['where'] .= $this->CI->sqlEscape(' and award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
	           // $data4['where'] .=$this->CI->sqlEscape('  and find_in_set(??,author.author) and find_in_set(??,author.identity)', array($name, $identity));
	            $data4['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Fourth[XPHEnum::Value].') author1','author1.data_id=db_awards.id');
                 $data4['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }

			$data4=$this->CI->Awards_model->get_list_full($data4);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Awards_model',$orderby,ResultsType::Fourth[XPHEnum::Caption]); //获取返回数据
			
		}		
		/* ------------------------------------研究报告、政策建议------------------------------------------------*/
		if(in_array(ResultsType::Fifth[XPHEnum::Value], $type)) {
			$data5['items']=$caseitems."if(db_research_report.submit_date='','--',FROM_UNIXTIME(db_research_report.submit_date,'%Y')) year_date";
			$data5['where']=$where;
            $data5['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Fifth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_research_report.id');
			if(isset($param['year']) && $param['year']!=''){
				$data5['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_research_report.submit_date,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data5['where'] .= $this->CI->sqlEscape(' and db_research_report.award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data5['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Fifth[XPHEnum::Value].') author1','author1.data_id=db_research_report.id');
                 $data5['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data5=$this->CI->Research_report_model->get_list_full($data5);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Research_report_model',$orderby,ResultsType::Fifth[XPHEnum::Caption]); //获取返回数据
		
		}
			/* ------------------------------------技术标准------------------------------------------------*/
			if(in_array(ResultsType::Sixth[XPHEnum::Value], $type)) {
				$data6['items']=$caseitems."if(db_technical_standard.publish_time='','--',FROM_UNIXTIME(db_technical_standard.publish_time,'%Y')) year_date";
				$data6['where']=$where;
	            $data6['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Sixth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_technical_standard.id');
				if(isset($param['year']) && $param['year']!=''){
					$data6['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_technical_standard.publish_time,'%Y')=??",array($param['year']));
				}
				if (isset($param['submit_year']) && $param['submit_year'] != '') {
	                $data6['where'] .= $this->CI->sqlEscape('  and db_technical_standard.award_year=?? ', array($param['submit_year']));
	            }
				if (isset($param['name'])  && $param['name']!=-1) {
					$name_arr = explode(",",$param['name']);
                   $name = $name_arr[0];
                   $identity = $name_arr[1];
                    $data6['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Sixth[XPHEnum::Value].') author1','author1.data_id=db_technical_standard.id');
                 $data6['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
		        }
				$data6=$this->CI->Technical_standard_model->get_list_full($data6);
				//重新拼接排序
				$sql = $this->CI->db->last_query();
				$data[] = $this->getTableData($sql,'Technical_standard_model',$orderby,ResultsType::Sixth[XPHEnum::Caption]); //获取返回数据
			}
	
		/* ------------------------------------纵向课题------------------------------------------------*/
		if(in_array(ResultsType::Seventh[XPHEnum::Value], $type)) {
			$data7['items']=$caseitems."if(db_vertical_project.project_time='','--',FROM_UNIXTIME(db_vertical_project.project_time,'%Y')) year_date";
			$data7['where']=$where;
            $data7['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Seventh[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_vertical_project.id');
			if(isset($param['year']) && $param['year']!=''){
				$data7['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_vertical_project.project_time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data7['where'] .= $this->CI->sqlEscape('  and db_vertical_project.award_year=??  ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data7['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Seventh[XPHEnum::Value].') author1','author1.data_id=db_vertical_project.id');
                 $data7['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data7=$this->CI->Vertical_project_model->get_list_full($data7);
				//重新拼接排序
				$sql = $this->CI->db->last_query();
				$data[] = $this->getTableData($sql,'Vertical_project_model',$orderby,ResultsType::Seventh[XPHEnum::Caption]); //获取返回数据
		
		}		
		/*------------------------------------横向课题------------------------------------------------*/
		if(in_array(ResultsType::Eighth[XPHEnum::Value], $type)) {
			$data8['items']=$caseitems."if(db_horizontal_project.project_time='','--',FROM_UNIXTIME(db_horizontal_project.project_time,'%Y')) year_date";
			$data8['where']=$where;
            $data8['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Eighth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_horizontal_project.id');
			if(isset($param['year']) && $param['year']!=''){
				$data8['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_horizontal_project.project_time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data8['where'] .= $this->CI->sqlEscape('  and db_horizontal_project.award_year=??  ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data8['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Eighth[XPHEnum::Value].') author1','author1.data_id=db_horizontal_project.id');
                 $data8['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	         //   $data8['where'] .= $this->CI->sqlEscape('  and find_in_set(??,author.author) and find_in_set(??,author.identity)', array($name, $identity));
	        }
			$data8=$this->CI->Horizontal_project_model->get_list_full($data8);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Horizontal_project_model',$orderby,ResultsType::Eighth[XPHEnum::Caption]); //获取返回数据
			
		}		
		/* ------------------------------------科技转化情况------------------------------------------------*/
		if(in_array(ResultsType::Ninth[XPHEnum::Value], $type)) {
			$data9['items']=$caseitems."if(db_science_conversion.time='','--',FROM_UNIXTIME(db_science_conversion.time,'%Y')) year_date";
			$data9['where']=$where;
            $data9['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Ninth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_science_conversion.id');
			if(isset($param['year']) && $param['year']!=''){
				$data9['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_science_conversion.time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data9['where'] .= $this->CI->sqlEscape(' and db_science_conversion.award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data9['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Ninth[XPHEnum::Value].') author1','author1.data_id=db_science_conversion.id');
                 $data9['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data9=$this->CI->Science_conversion_model->get_list_full($data9);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Science_conversion_model',$orderby,ResultsType::Ninth[XPHEnum::Caption]); //获取返回数据
			
		}		
		/* ------------------------------------举办、参加学术交流会、学会年会------------------------------------------------*/
		if(in_array(ResultsType::Tenth[XPHEnum::Value], $type)) {
			$data10['items']=$caseitems."if(time='','--',FROM_UNIXTIME(time,'%Y')) year_date";
			$data10['where']=$where;
            $data10['join']=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Tenth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_communication.id');
			if(isset($param['year']) && $param['year']!=''){
				$data10['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data10['where'] .= $this->CI->sqlEscape('  and award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				// 没有成果有人
	            $data10['where'] .= $this->CI->sqlEscape(' and 1<>?? ', array(1));
	        }
			$data10=$this->CI->Communication_model->get_list_full($data10);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Communication_model',$orderby,ResultsType::Tenth[XPHEnum::Caption]); //获取返回数据
		
		}		
		/* ------------------------------------学术报告情况------------------------------------------------*/
		if(in_array(ResultsType::Eleventh[XPHEnum::Value], $type)) {
			$data11['items']=$caseitems."if(time='','--',FROM_UNIXTIME(time,'%Y')) year_date";
			$data11['where']=$where;
            $data11['join']=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Twelfth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_academic_report.id');
			if(isset($param['year']) && $param['year']!=''){
				$data11['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data11['where'] .= $this->CI->sqlEscape('  and award_year=?? ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				// 没有成果有人
	            $data11['where'] .= $this->CI->sqlEscape(' and 1<>?? ', array(1));
	        }
			$data11=$this->CI->Academic_report_model->get_list_full($data11);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Academic_report_model',$orderby,ResultsType::Eleventh[XPHEnum::Caption]); //获取返回数据
			
		}		
		/* ------------------------------------学术兼职情况------------------------------------------------*/
		if(in_array(ResultsType::Twelfth[XPHEnum::Value], $type)) {
			$data12['items']=$caseitems." if(db_academia_job.created='','--',FROM_UNIXTIME(db_academia_job.created,'%Y')) year_date";
			$data12['where']=$where;
            $data12['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Twelfth[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_academia_job.id');
			if(isset($param['year']) && $param['year']!=''){
				$data12['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_academia_job.created,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data12['where'] .= $this->CI->sqlEscape('  and db_academia_job.award_year=??  ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data12['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Twelfth[XPHEnum::Value].') author1','author1.data_id=db_academia_job.id');
                 $data12['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data12=$this->CI->Academia_job_model->get_list_full($data12);
			//重新拼接排序
			$sql = $this->CI->db->last_query();
			$data[] = $this->getTableData($sql,'Academia_job_model',$orderby,ResultsType::Twelfth[XPHEnum::Caption]); //获取返回数据
			
		}		
		/* ------------------------------------学科平台------------------------------------------------*/
		if(in_array(ResultsType::Thirteen[XPHEnum::Value], $type)) {
			$data13['items']=$caseitems."if(db_subject_platform.receive_time='','--', FROM_UNIXTIME(db_subject_platform.receive_time,'%Y')) year_date";
			$data13['where']=$where;
            $data13['join'][0]=array('(select group_concat(db_author.author_name) author,group_concat(db_author.identity) identity,data_id FROM db_author WHERE db_author.type = '.ResultsType::Thirteen[XPHEnum::Value].'  GROUP BY db_author.data_id ) author','author.data_id=db_subject_platform.id');
			if(isset($param['year']) && $param['year']!=''){
				$data13['where'].=$this->CI->sqlEscape(" and FROM_UNIXTIME(db_subject_platform.receive_time,'%Y')=??",array($param['year']));
			}
			if (isset($param['submit_year']) && $param['submit_year'] != '') {
                $data13['where'] .= $this->CI->sqlEscape(' and db_subject_platform.award_year=??  ', array($param['submit_year']));
            }
			if (isset($param['name'])  && $param['name']!=-1) {
				$name_arr = explode(",",$param['name']);
                $name = $name_arr[0];
                $identity = $name_arr[1];
                 $data13['join'][1]=array('(select * from db_author where db_author.type='.ResultsType::Thirteen[XPHEnum::Value].') author1','author1.data_id=db_subject_platform.id');
                 $data13['where'] .= $this->CI->sqlEscape(' and find_in_set(??,author1.author_name) and find_in_set(??,author1.identity) ', array($name,$identity));
	        }
			$data13=$this->CI->Subject_platform_model->get_list_full($data13);
				//重新拼接排序
				$sql = $this->CI->db->last_query();
				$data[] = $this->getTableData($sql,'Subject_platform_model',$orderby,ResultsType::Thirteen[XPHEnum::Caption]); //获取返回数据
			
		}	
		
		// var_dump($data);
		if(!isset($data)){
			$data=array();
		}

		return retJson('图标数据',true,$data);
	}

	//重新排列顺序，获取统计值

	function getTableData($sql,$model,$orderby,$type){
	
		$sql1 = "select *,count(*)as count from (".$sql.")  p  GROUP BY year_date,dept_name order by ".$orderby;
		$data1=$this->CI->$model->query($sql1);
		$year_list = array_values(array_unique(array_column($data1,'year_date'))) ;
		$dept_list = array_values(array_unique(array_column($data1,'dept_name')));
		rsort($year_list); //按年份降序排列
		if($data1){
			$result=array();				
			foreach($data1 as $val){
					$key = $val['dept_name'].'_'.$val['year_date'];
					$result[$key] = $val['count'];
			}
			$data1_score = array();
			foreach($dept_list as $k=>$v){
				foreach($year_list as $k1=>$v1){
					if(isset($result[$v.'_'.$v1])){
						$data1_score[$v]['data'][] = $result[$v.'_'.$v1];
					}else{
						$data1_score[$v]['data'][] = 0;
					}
				}
				$data1_score[$v]['name'] = $v;
				$data1_score[$v]['type'] = 'bar';
				$data1_score[$v]['color'] = (new Dept())->showRemark($v);
				
			}

			return array('table_name'=>reset($year_list).'-'.end($year_list).$type,'data'=>$data1_score,'title'=>$year_list,'dept_name'=>$dept_list);
		}else{
			return array();
		}	

	}

}